import mysql.connector
from mysql.connector import Error


def get_specification_ids_by_dish_id(target_dish_id):
    """根据 dish_id 查询所有关联的 flavor_id"""
    specification_ids = []
    try:
        # 连接数据库配置（根据实际情况修改）
        connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="123456",
            database="esl"
        )

        if connection.is_connected():
            cursor = connection.cursor()

            # 直接查询 dish_flavor 表
            query = """
            SELECT specification_id 
            FROM dish_specification 
            WHERE dish_id = %s
            """
            cursor.execute(query, (target_dish_id,))

            # 提取所有 specification_ids（跳过空值）
            specification_ids = [row[0] for row in cursor.fetchall() if row[0]]

    except Error as e:
        print("数据库错误:", e)
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
    return specification_ids


# 使用示例
if __name__ == "__main__":
    target_dish_id = "22c78404726e4fca8c8b812826771d42"  # 替换为你的实际 dish_id

    result = get_specification_ids_by_dish_id(target_dish_id)

    if result:
        print(f"菜品 {target_dish_id} 包含口味ID：", result)
    else:
        print(f"未找到菜品 {target_dish_id} 的口味信息")